use crate::structs::Operator;
use crate::{
    base::mysql::get_columns,
    prelude::{
        get_my_pool, get_page_config,
        options::Options,
        report::{DisplayCols, Unit},
        Column, CommonPage, DataType, DbType, DictKeyValue, Direction, FieldType, OrderItems,
        WhereCause, WhereCauses,
    },
    utils::json_value_to_string,
};
use serde_json::{Map, Value};
use sqlx::{MySql, Pool, Row};
use std::collections::HashMap;
use num_traits::ToPrimitive;

use super::Callback;
pub struct MysqlDatabase {
    conf: &'static CommonPage,
    table_name: &'static str,
    callback: Option<Box<dyn Callback>>,
    tx: Option<sqlx::Transaction<'static, sqlx::MySql>>,
}
impl MysqlDatabase {
    pub fn from(conf_name: &str) -> anyhow::Result<Self> {
        let conf = get_page_config(conf_name)?;
        if conf.table_name.is_none() {
            return Err(anyhow::anyhow!("配置文件{}没有配置表名", conf_name));
        }
        let table_name = conf.table_name.as_deref().unwrap();
        Ok(MysqlDatabase {
            conf,
            table_name,
            tx: None,
            callback: None,
        })
    }
    pub fn from_with_tx(
        conf_name: &str,
        callback: Option<Box<dyn Callback>>,
        tx: sqlx::Transaction<'static, sqlx::MySql>,
    ) -> anyhow::Result<Self> {
        let conf = get_page_config(conf_name)?;
        if conf.table_name.is_none() {
            return Err(anyhow::anyhow!("配置文件{}没有配置表名", conf_name));
        }
        let table_name = conf.table_name.as_deref().unwrap();
        Ok(MysqlDatabase {
            conf,
            table_name,
            tx: Some(tx),
            callback,
        })
    }
    pub async fn create_by_json(&mut self, map_val: Map<String, Value>) -> anyhow::Result<u64> {
        let sql = self
            .conf
            .insert_sql
            .as_ref()
            .ok_or(anyhow::anyhow!("没有配置insert_sql"))?;
        let mut map_val = map_val;
        let mut exector = sqlx::query(sql);
        if let Some(callback) = &mut self.callback {
            let tx = self
                .tx
                .as_mut()
                .ok_or(anyhow::anyhow!("执行callback没有事务"))?;
            map_val = callback.before_insert(map_val, tx).await?;
            map_val = callback.before_save(map_val, tx).await?;
        }
        for fp in self.conf.get_leaf_columns().iter() {
            if fp.is_pk && fp.is_auto_incr {
                continue;
            }
            let col_name = fp.name.as_deref().unwrap();
            let v = map_val
                .get(col_name)
                .ok_or(anyhow::anyhow!("行数据中没有提供字段{}的值", col_name))?;
            if let Some(val) = v.as_str() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_i64() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_f64() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_bool() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_str() {
                exector = exector.bind(val);
            } else {
                let val:Option<String>=None;
                exector = exector.bind(val);
            }
        }
        let res = if let Some(tx) = &mut self.tx {
            exector.execute(&mut **tx).await?
        } else {
            exector.execute(get_my_pool()).await?
        };
        let id: u64 = res.last_insert_id();
        map_val.insert("id".to_string(), Value::Number(id.into()));
        if let Some(callback) = &mut self.callback {
            let tx = self
                .tx
                .as_mut()
                .ok_or(anyhow::anyhow!("执行callback没有事务"))?;
            callback.after_save(map_val, tx).await?;
        }
        Ok(id)
    }
    pub async fn update_part_by_json(
        &mut self,
        mut map_val: Map<String, Value>,
    ) -> anyhow::Result<u64> {
        if let Some(callback) = &mut self.callback {
            let tx = self
                .tx
                .as_mut()
                .ok_or(anyhow::anyhow!("执行callback没有事务"))?;
            map_val = callback
                .before_update(map_val, tx)
                .await
                .or_else(|e| Err(anyhow::anyhow!("callback:{}", e.to_string())))?;
            map_val = callback
                .before_save(map_val, tx)
                .await
                .or_else(|e| Err(anyhow::anyhow!("callback:{}", e.to_string())))?;
        }
        let mut sql = "update ".to_owned();
        sql.push_str(self.table_name);
        sql.push_str(" set ");
        let mut values: Vec<&Value> = Vec::new();
        let mut pk_val: Option<u64> = None;
        for fp in self.conf.get_leaf_columns().iter() {
            let col_name = fp.name.as_deref().unwrap();
            let v = map_val.get(col_name);
            if v.is_none() {
                continue;
            }
            let v = v.unwrap();
            if fp.is_pk && fp.is_auto_incr {
                match v {
                    Value::Number(num) => {
                        pk_val = Some(num.as_u64().ok_or(anyhow::anyhow!("主键值必须是数字"))?);
                    }
                    Value::String(s) => {
                        pk_val = Some(
                            s.parse::<u64>()
                                .map_err(|_| anyhow::anyhow!("主键值必须是数字"))?,
                        );
                    }
                    _ => {
                        return Err(anyhow::anyhow!("主键值必须是数字"));
                    }
                }
            } else {
                values.push(v);
                sql.push_str(&format!("`{}` = ?,", col_name));
            }
        }
        sql.pop(); // 移除最后的逗号
        let pk_val = pk_val.ok_or(anyhow::anyhow!("没有提供主键值"))?;
        if values.is_empty() {
            return Err(anyhow::anyhow!("没有提供需要更新的字段"));
        }
        sql.push_str(&format!(" WHERE id = ?"));
        let mut exector = sqlx::query(&sql);
        for v in values.iter() {
            if let Some(val) = v.as_str() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_i64() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_u64() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_f64() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_bool() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_str() {
                exector = exector.bind(val);
            } else {
                return Err(anyhow::anyhow!("不支持的值类型"));
            }
        }
        exector = exector.bind(pk_val);
        if let Some(tx) = &mut self.tx {
            exector.execute(&mut **tx).await?;
        } else {
            exector.execute(get_my_pool()).await?;
        }
        Ok(pk_val as u64)
    }
    pub async fn update_by_json(&mut self, map_val: Map<String, Value>) -> anyhow::Result<u64> {
        let sql = self
            .conf
            .update_sql
            .as_ref()
            .ok_or(anyhow::anyhow!("没有配置update_sql"))?;
        let mut map_val = map_val;
        let mut exector = sqlx::query(sql);
        if let Some(callback) = &mut self.callback {
            let tx = self
                .tx
                .as_mut()
                .ok_or(anyhow::anyhow!("执行callback没有事务"))?;
            map_val = callback.before_update(map_val, tx).await?;
            map_val = callback.before_save(map_val, tx).await?;
        }
        let mut pk_col: Option<u64> = None;
        for fp in self.conf.get_leaf_columns().iter() {
            let col_name = fp.name.as_deref().unwrap();
            let v = map_val
                .get(col_name)
                .ok_or(anyhow::anyhow!("行数据中没有提供字段{}的值", col_name))?;
            if fp.is_pk && fp.is_auto_incr {
                pk_col = Some(v.as_u64().ok_or(anyhow::anyhow!("主键值必须是数字"))?);
                continue;
            }
            if let Some(val) = v.as_str() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_i64() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_u64() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_f64() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_bool() {
                exector = exector.bind(val);
            } else if let Some(val) = v.as_str() {
                exector = exector.bind(val);
            } else {
                return Err(anyhow::anyhow!("不支持的值类型"));
            }
        }
        let pk_val = pk_col.ok_or(anyhow::anyhow!("没有提供主键值"))?;
        exector = exector.bind(pk_val);
        if let Some(tx) = &mut self.tx {
            exector.execute(&mut **tx).await?;
        } else {
            exector.execute(get_my_pool()).await?;
        }
        if let Some(callback) = &mut self.callback {
            let tx = self
                .tx
                .as_mut()
                .ok_or(anyhow::anyhow!("执行callback没有事务"))?;
            callback.after_save(map_val, tx).await?;
        }
        Ok(pk_val as u64)
    }
    pub async fn save_by_json(&mut self, map_val: Map<String, Value>) -> anyhow::Result<u64> {
        let pk_col = self
            .conf
            .field_props
            .iter()
            .find(|fp| fp.is_pk && fp.is_auto_incr)
            .ok_or(anyhow::anyhow!("没有配置主键"))?;
        let col_name = pk_col.name.as_deref().unwrap();
        let v = map_val.get(col_name);
        if v.is_none() {
            self.create_by_json(map_val).await
        } else {
            self.update_by_json(map_val).await
        }
    }
    pub async fn delete_by_id(&mut self, id: u64) -> anyhow::Result<u64> {
        let sql = format!("DELETE FROM {} WHERE id = ?", self.table_name);
        let exector = sqlx::query(&sql).bind(id);
        if let Some(callback) = &mut self.callback {
            let tx = self
                .tx
                .as_mut()
                .ok_or(anyhow::anyhow!("执行callback没有事务"))?;
            callback.before_delete(&[id], tx).await?;
        }
        let res = match &mut self.tx {
            Some(tx) => exector.execute(&mut **tx).await?,
            None => exector.execute(get_my_pool()).await?,
        };
        if let Some(callback) = &mut self.callback {
            let tx = self
                .tx
                .as_mut()
                .ok_or(anyhow::anyhow!("执行callback没有事务"))?;
            callback.after_delete(&[id], tx).await?;
        }
        Ok(res.rows_affected())
    }
    pub async fn delete_by_ids(&mut self, ids: &[u64]) -> anyhow::Result<u64> {
        let sql = format!(
            "DELETE FROM {} WHERE id IN ({})",
            self.table_name,
            ids.iter()
                .map(|id| id.to_string())
                .collect::<Vec<_>>()
                .join(",")
        );
        if let Some(callback) = &mut self.callback {
            let tx = self
                .tx
                .as_mut()
                .ok_or(anyhow::anyhow!("执行callback没有事务"))?;
            callback.before_delete(ids, tx).await?;
        }
        let exector = sqlx::query(&sql);
        let res = match &mut self.tx {
            Some(tx) => exector.execute(&mut **tx).await?,
            None => exector.execute(get_my_pool()).await?,
        };
        if let Some(callback) = &mut self.callback {
            let tx = self
                .tx
                .as_mut()
                .ok_or(anyhow::anyhow!("执行callback没有事务"))?;
            callback.after_delete(ids, tx).await?;
        }
        Ok(res.rows_affected())
    }
    pub async fn delete_by_map(
        &mut self,
        para_vals: &HashMap<String, String>,
    ) -> anyhow::Result<u64> {
        let mut sql = format!("DELETE FROM {} WHERE ", self.table_name);
        let mut params: Vec<&str> = Vec::new();
        let plen = para_vals.len();
        for (i, (key, value)) in para_vals.iter().enumerate() {
            sql.push_str(&format!("{} = ?", key));
            if i < plen - 1 {
                sql.push_str(" AND ");
            }
            params.push(value);
        }
        let mut exector = sqlx::query(&sql);
        for p in params.iter() {
            exector = exector.bind(p);
        }
        let res = match &mut self.tx {
            Some(tx) => exector.execute(&mut **tx).await?,
            None => exector.execute(get_my_pool()).await?,
        };
        Ok(res.rows_affected())
    }
    pub async fn find_by_id(&mut self, id: u64) -> anyhow::Result<Option<Map<String, Value>>> {
        let sel_sql = self
            .conf
            .select_sql
            .as_ref()
            .ok_or(anyhow::anyhow!("没有配置select_sql"))?;
        let sql = format!("{sel_sql} WHERE id = ?");
        let exector = sqlx::query(&sql).bind(id);
        let res = match &mut self.tx {
            Some(tx) => exector.fetch_optional(&mut **tx).await?,
            None => exector.fetch_optional(get_my_pool()).await?,
        };
        if let Some(row) = res {
            let leafs = self.conf.get_leaf_columns();
            let map_val = mysql_row_to_json(&row, &leafs)
                .map_err(|e| anyhow::anyhow!("mysql_row_to_json error: {}", e))?;
            Ok(Some(map_val))
        } else {
            Ok(None)
        }
    }
    pub async fn find_id_by_uniqe_keys(
        &mut self,
        para_vals: HashMap<&str, String>,
    ) -> anyhow::Result<Option<u64>> {
        let mut sql = format!("SELECT id FROM {} WHERE ", self.table_name);
        let mut params: Vec<&str> = Vec::new();
        let plen = para_vals.len();
        for (i, (key, value)) in para_vals.iter().enumerate() {
            sql.push_str(&format!("{} = ?", key));
            params.push(value);
            if i < plen - 1 {
                sql.push_str(" AND ");
            }
        }
        let exector = sqlx::query(&sql);
        let res = match &mut self.tx {
            Some(tx) => exector.fetch_optional(&mut **tx).await?,
            None => exector.fetch_optional(get_my_pool()).await?,
        };
        if let Some(row) = res {
            let id: u64 = row.get(0);
            Ok(Some(id))
        } else {
            Ok(None)
        }
    }
    pub async fn find_json_by_uniqe_keys(
        &mut self,
        para_vals: HashMap<String, String>,
    ) -> anyhow::Result<Option<Map<String, Value>>> {
        let sel_sql = self
            .conf
            .select_sql
            .as_ref()
            .ok_or(anyhow::anyhow!("没有配置select_sql"))?;
        let mut sql = format!("{sel_sql} WHERE ");
        let mut params: Vec<&str> = Vec::new();
        let plen = para_vals.len();
        for (i, (key, value)) in para_vals.iter().enumerate() {
            sql.push_str(&format!("{} = ?", key));
            params.push(value);
            if i < plen - 1 {
                sql.push_str(" AND ");
            }
        }
        let mut exector = sqlx::query(&sql);
        for p in params.iter() {
            exector = exector.bind(p);
        }
        let res = match &mut self.tx {
            Some(tx) => exector.fetch_optional(&mut **tx).await?,
            None => exector.fetch_optional(get_my_pool()).await?,
        };
        let leafs = self.conf.get_leaf_columns();
        if let Some(row) = res {
            let map_val = mysql_row_to_json(&row, &leafs)
                .map_err(|e| anyhow::anyhow!("mysql_row_to_json error: {}", e))?;
            Ok(Some(map_val))
        } else {
            Ok(None)
        }
    }
    pub async fn find_in_page(
        &mut self,
        table_name: Option<String>,
        where_causes: Option<WhereCauses>,
        auth_filter: Option<&str>,
        orders: Option<OrderItems>,
        display_cols: &DisplayCols,
        page: u64,
        page_size: u64,
    ) -> anyhow::Result<(Vec<serde_json::Value>, u64)> {
        let where_causes = if let Some(wcs) = where_causes {
            let cols = self.conf.get_leaf_columns();
            let wcs = convert_where_causes(cols.as_slice(), wcs)?;
            Some(wcs)
        } else {
            None
        };
        let mut sel_sql = self
            .conf
            .select_sql
            .as_ref()
            .ok_or(anyhow::anyhow!("没有配置select_sql"))?
            .to_owned();
        let table_name2 = if let Some(table_name) = table_name {
            sel_sql = sel_sql.replace(self.table_name, &table_name);
            table_name
        } else {
            self.table_name.to_owned()
        };
        let mut count_sql = format!("SELECT COUNT(*) FROM {}", table_name2);
        let leafs = self.conf.get_leaf_columns();
        let pool = get_my_pool();
        let mut where_sql = String::new();
        let mut order_sql = String::new();
        let mut add_where = |wstr: &str| {
            if where_sql.is_empty() {
                where_sql.push_str(" WHERE ");
                where_sql.push_str(&wstr);
            } else {
                where_sql.push_str(" AND ");
                where_sql.push_str(&wstr);
            }
        };
        let mut add_order = |ostr: String| {
            if order_sql.is_empty() {
                order_sql.push_str(" ORDER BY ");
                order_sql.push_str(&ostr);
            } else {
                order_sql.push_str(",");
                order_sql.push_str(&ostr);
            }
        };
        let mut dicts: Vec<(&str, &str, &Options)> = Vec::new();
        for leaf in leafs.iter() {
            let cname = leaf.name.as_deref().unwrap_or_default();
            let dict_col_name = leaf.dict_col_name.as_ref();
            let wc = where_causes
                .as_ref()
                .and_then(|wcs| wcs.get_cause_by_field_name(cname));
            let ord = orders
                .as_ref()
                .and_then(|ords| ords.get_item_by_field_name(cname));
            if let Some(dict_col_name) = dict_col_name {
                let options = leaf.edit_props.as_ref().unwrap().options.as_ref().unwrap();
                dicts.push((cname, dict_col_name, options));
            }
            if wc.is_none() && ord.is_none() {
                continue;
            }
            let is_not_dict = wc.map(|wc| wc.is_in() || wc.is_none()).unwrap_or(false);
            let fval = if dict_col_name.is_some() {
                let dict_col_name = dict_col_name.unwrap();
                let options = leaf.edit_props.as_ref().unwrap().options.as_ref().unwrap();
                if !is_not_dict {
                    let fval = get_filter_value(
                        dict_col_name.as_str(),
                        &leaf.field_type,
                        options,
                        wc.as_deref(),
                        ord.map(|o| o.direction.clone()),
                        pool,
                    )
                    .await?;
                    Some(fval)
                } else {
                    None
                }
            } else {
                None
            };
            if let Some(wc) = wc {
                let wstr = wc.to_where_string(
                    DbType::MySql,
                    leaf.data_type,
                    &Unit::Yuan,
                    leaf.is_null,
                    fval.as_ref(),
                );
                add_where(&wstr);
            }
            if let Some(ord) = ord {
                let ostr = ord.to_order_string(fval.as_ref());
                add_order(ostr);
                if let Some(DictKeyValue::Table(tname)) = &fval {
                    sel_sql.push_str(&format!(
                        " LEFT JOIN `{}` ON `{}`.`{}` = `{}`.`key_value` ",
                        tname, table_name2, cname, tname
                    ));
                }
            }
        }
        if let Some(ref af) = auth_filter {
            add_where(af);
        }
        sel_sql.push_str(where_sql.as_str());
        count_sql.push_str(where_sql.as_str());
        sel_sql.push_str(order_sql.as_str());
        sel_sql.push_str(&format!(" LIMIT {}, {}", (page - 1) * page_size, page_size));
        let exector = sqlx::query(&sel_sql);
        let res = match &mut self.tx {
            Some(tx) => exector.fetch_all(&mut **tx).await?,
            None => exector.fetch_all(pool).await?,
        };
        let mut values: Vec<serde_json::Value> = Vec::new();
        for row in res {
            let mut map_val = mysql_row_to_json(&row, &leafs)
                .map_err(|e| anyhow::anyhow!("mysql_row_to_json error: {}", e))?;
            for (name, dict_col_name, options) in dicts.iter() {
                let dict_col_name = dict_col_name;
                let jval = map_val
                    .get(*name)
                    .ok_or(anyhow::anyhow!("行数据中没有提供字段{}的值", name))?;
                let key = json_value_to_string(jval);
                let label = options.get_label_by_key(&key).await?.unwrap_or_default();
                map_val.insert(dict_col_name.to_string(), label.into());
            }
            if let Some(callback) = &mut self.callback {
                let tx = self
                    .tx
                    .as_mut()
                    .ok_or(anyhow::anyhow!("执行callback没有事务"))?;
                map_val = callback.after_find(map_val, tx).await?;
            }
            values.push(Value::Object(map_val));
        }
        let exector = sqlx::query(&count_sql);
        let res = match &mut self.tx {
            Some(tx) => exector.fetch_one(&mut **tx).await?,
            None => exector.fetch_one(pool).await?,
        };
        let count: i64 = res.try_get(0)?;
        Ok((values, count as u64))
    }
    pub async fn commit(&mut self) -> anyhow::Result<()> {
        if let Some(tx) = std::mem::take(&mut self.tx) {
            tx.commit().await?;
        }
        Ok(())
    }
}
fn convert_where_causes(cols: &[&Column], mut wcs: WhereCauses) -> anyhow::Result<WhereCauses> {
    for wc in wcs.as_mut() {
        if let Some(col) = cols
            .iter()
            .find(|c| c.name.as_deref() == Some(wc.field_name.as_str()))
        {
            for fi in wc.filter_items.iter_mut() {
                if fi.operator == Operator::In || fi.operator == Operator::NotIn {
                    continue;
                }
                if col.data_type == DataType::Int || col.data_type == DataType::Float {
                    if let Some(val) = &fi.value {
                        let is_perc = col
                            .list_props
                            .as_ref()
                            .map(|lp| lp.format.as_ref().map(|f| f.ends_with('%')))
                            .flatten()
                            .unwrap_or(false);
                        if !is_perc {
                            continue;
                        }
                        let unit = col.unit.to_u32() as f64;
                        let fv = val.parse::<f64>();
                        if fv.is_ok() {
                            continue;
                        }
                        let mut fv = fv.unwrap();
                        if is_perc {
                            fv = fv / 100.0;
                        } else {
                            fv = fv / unit;
                        }
                        if col.data_type == DataType::Int {
                            let iv = fv as i64;
                            fi.value = Some(iv.to_string());
                        } else if col.data_type == DataType::Float {
                            fi.value = Some(fv.to_string());
                        }
                    }
                }
            }
        }
    }
    Ok(wcs)
}
pub(crate) fn mysql_row_to_json(
    row: &sqlx::mysql::MySqlRow,
    field_props: &[&Column],
) -> anyhow::Result<Map<String, Value>> {
    let cols = field_props
        .iter()
        .map(|fp| (fp.name.as_deref().unwrap(), &fp.field_type))
        .collect::<Vec<_>>();
    mysql_row_to_json2(row, &cols)
}

pub(crate) fn mysql_row_to_string<'a>(
    row: &'a sqlx::mysql::MySqlRow,
    field_props: &'a [(&'a str, &'a FieldType)],
) -> anyhow::Result<Vec<String>> {
    let mut res: Vec<String> = Vec::new();
    for (name, data_type) in field_props.iter() {
        let col_name = *name;
        match data_type {
            crate::prelude::FieldType::Bool => {
                let v: bool = row.try_get(col_name)?;
                let bv = if v { "1" } else { "0" };
                res.push(bv.to_owned());
            }
            crate::prelude::FieldType::UInt8 => {
                let v: u8 = row.try_get(col_name)?;
                res.push(v.to_string());
            }
            crate::prelude::FieldType::UInt16 => {
                let v: Option<u16> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |v| v.to_string()));
            }
            crate::prelude::FieldType::UInt32 => {
                let v: Option<u32> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |v| v.to_string()));
            }
            crate::prelude::FieldType::UInt64 => {
                let v: Option<u64> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |v| v.to_string()));
            }
            crate::prelude::FieldType::Int8 => {
                let v: Option<i8> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |v| v.to_string()));
            }
            crate::prelude::FieldType::Int16 => {
                let v: Option<i16> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |v| v.to_string()));
            }
            crate::prelude::FieldType::Int32 => {
                let v: Option<i32> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |v| v.to_string()));
            }
            crate::prelude::FieldType::Int64 => {
                let v: Option<i64> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |v| v.to_string()));
            }
            crate::prelude::FieldType::Float32 => {
                let v: Option<f32> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |v| v.to_string()));
            }
            crate::prelude::FieldType::Float64 => {
                let v: Option<f64> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |v| v.to_string()));
            }
            crate::prelude::FieldType::Decimal(_, _) => {
                let v: Option<f64> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |v| v.to_string()));
            }
            crate::prelude::FieldType::String(_) => {
                let v: Option<String> = row.try_get(col_name)?;
                res.push(v.unwrap_or_default());
            }
            crate::prelude::FieldType::Date => {
                let v: Option<chrono::NaiveDate> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |d| d.format("%Y-%m-%d").to_string()));
            }
            crate::prelude::FieldType::Datetime => {
                let v: Option<chrono::NaiveDate> = row.try_get(col_name)?;
                res.push(v.map_or("".to_owned(), |d| d.format("%Y-%m-%d %H:%M:%S").to_string()));
            }
        }
    }
    Ok(res)
}

pub(crate) fn mysql_row_to_json2(
    row: &sqlx::mysql::MySqlRow,
    field_props: &[(&str, &FieldType)],
) -> anyhow::Result<Map<String, Value>> {
    let mut map_val: Map<String, Value> = Map::new();
    for (name, data_type) in field_props.iter() {
        let col_name = *name;
        match data_type {
            crate::prelude::FieldType::Bool => {
                let v: Option<bool> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::UInt8 => {
                let v: Option<u8> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::UInt16 => {
                let v: Option<u16> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::UInt32 => {
                let v: Option<u32> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::UInt64 => {
                let v: Option<u64> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::Int8 => {
                let v: Option<i8> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::Int16 => {
                let v: Option<i16> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::Int32 => {
                let v: Option<i32> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::Int64 => {
                let v: Option<i64> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::Float32 => {
                let v: Option<f32> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::Float64 => {
                let v: Option<f64> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::Decimal(_, _) => {
                let v: Option<rust_decimal::Decimal> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v.map(|d| d.to_f64())) );
            }
            crate::prelude::FieldType::String(_) => {
                let v: Option<String> = row.try_get(col_name)?;
                map_val.insert(col_name.into(), Value::from(v));
            }
            crate::prelude::FieldType::Date => {
                let v: Option<chrono::NaiveDate> = row.try_get(col_name)?;
                map_val.insert(
                    col_name.into(),
                    Value::from(v.map(|v| v.format("%Y-%m-%d").to_string())),
                );
            }
            crate::prelude::FieldType::Datetime => {
                let v: Option<chrono::NaiveDateTime> = row.try_get(col_name)?;
                map_val.insert(
                    col_name.into(),
                    v.map_or(Value::Null, |d| {
                        Value::from(d.format("%Y-%m-%d %H:%M:%S").to_string())
                    }),
                );
            }
        }
    }
    Ok(map_val)
}

pub async fn find_list_data(
    table_name: &str,
    sel_cols: &[&str],
    where_causes: Option<&str>,
    orders: Option<&str>,
) -> anyhow::Result<Vec<serde_json::Value>> {
    let mut sel_sql = format!("SELECT {} FROM {}", sel_cols.join(","), table_name);
    let columns = get_columns(table_name).await?;
    if let Some(wcs) = where_causes {
        sel_sql.push_str(&format!(" WHERE {} ", wcs));
    }
    if let Some(orders) = orders {
        sel_sql.push_str(&format!(" ORDER BY {} ", orders));
    }
    let exector = sqlx::query(&sel_sql);
    let res = exector.fetch_all(get_my_pool()).await?;
    let props: Vec<(&str, &FieldType)> = columns
        .iter()
        .filter(|c| sel_cols.contains(&c.name.as_str()))
        .map(|fp| (fp.name.as_str(), &fp.field_type))
        .collect();
    let mut values: Vec<serde_json::Value> = Vec::new();
    for row in res {
        let map_val = mysql_row_to_json2(&row, props.as_slice())?;
        values.push(Value::Object(map_val));
    }
    Ok(values)
}

async fn get_filter_value(
    dict_col_name: &str,
    ft: &FieldType,
    opts: &Options,
    where_cause: Option<&WhereCause>,
    order: Option<Direction>,
    pool: &Pool<MySql>,
) -> anyhow::Result<DictKeyValue> {
    let keys = opts.get_keys_by_label(where_cause, order).await?;
    let fval = if keys.len() > 500 {
        let sql = format!(
            r#"
        CREATE TEMPORARY TABLE temp_{dict_col_name} (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `key_value` {} NOT NULL,
        )
        "#,
            ft.to_mysql_type()
        );
        sqlx::query(&sql).execute(pool).await?;
        let mut tx = pool.begin().await?;
        for vals in keys.chunks(500) {
            let chunk_sql = vals
                .iter()
                .map(|key| format!("('{}')", key))
                .collect::<Vec<_>>()
                .join(",");
            let sql = format!(
                "INSERT INTO temp_{dict_col_name} (key_value) VALUES {}",
                chunk_sql
            );
            sqlx::query(&sql).execute(&mut *tx).await?;
        }
        tx.commit().await?;
        DictKeyValue::Table(format!("temp_{dict_col_name}"))
    } else {
        DictKeyValue::Keys(keys)
    };
    Ok(fval)
}
